package Utils;

import Service.DBService;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.temporal.Temporal;

/**
 * @author 文永达
 * @title: update
 * @projectName jiaowuglxt
 * @description: TODO
 * @date 2021/1/20 11:37
 */
public class UpdateUtil {
    DBService dbService = new DBService();

       /**
        * @description: <br>
        * 〈开学添加新班〉
        * @Param: [majorname, clazzname, gradename, num]
        * @Return: int
        * @Author: 文永达
        * @Date: 2021/1/21 10:43
        */
    public int addnewCLazz(String majorname,String clazzname,String gradename,int num){
        try {
            if(num==1){//年级和专业都有
                String sql1_1 = "SELECT DISTINCT gradeid FROM clazz WHERE gradename = '"+gradename+"'";
                String sql1_2 = "SELECT DISTINCT majorid FROM clazz WHERE majorname = '"+majorname+"'";
                ResultSet rs1_1 = dbService.querySQL(sql1_1);
                ResultSet rs1_2 = dbService.querySQL(sql1_2);
                if(rs1_1.next()&&rs1_2.next()){
                    int majorid = rs1_2.getInt("majorid");
                    int gradeid = rs1_1.getInt("gradeid");
                    String sql3 = "select gradename,majorname,clazzname from clazz where clazzname = '"+clazzname+"' and gradename = '"+gradename+"' and majorname = '"+majorname+"'";
                    ResultSet rs3 = dbService.querySQL(sql3);
                    if(!rs3.next()){
                        String sql2 = "insert into clazz(majorid,majorname,clazzname,gradeid,gradename) values('"+majorid+"','"+majorname+"','"+clazzname+"','"+gradeid+"','"+gradename+"')";
                        int i = dbService.updateSQL(sql2);
                        if(i!=0) {
                            System.out.println("操作成功");
                        }else {
                            System.out.println("操作失败");
                        }
                    }else {
                        System.out.println("此专业已经存在此班级，无法开班");
                    }

                } else {
                    System.out.println("没有找到此专业，请确认专业是否存在");
                    return 0;
                }
            } else {
                String sql4 = "select gradeid from clazz where gradename = '"+gradename+"'";
                ResultSet rs1 = dbService.querySQL(sql4);//获取年级

                String sql5 = "select majorid from clazz where majorname = '"+majorname+"'";
                ResultSet rs2 = dbService.querySQL(sql5);//获取专业

                if(rs1.next()&&!rs2.next()){//年级有了,专业没有
                    String sqlMaxmajorid = "select max(majorid) from clazz";
                    ResultSet rsMaxmajorid = dbService.querySQL(sqlMaxmajorid);
                    rsMaxmajorid.next();
                    int majorid = rsMaxmajorid.getInt(1)+1;
                    int gradeid = rs1.getInt(1);
                    String sql6 = "insert into clazz(majorid,majorname,clazzname,gradeid,gradename) values('"+majorid+"','"+majorname+"','"+clazzname+"','"+gradeid+"','"+gradename+"')";
                    int i = dbService.updateSQL(sql6);
                    if(i!=0){
                        System.out.println("操作成功");
                    }else {
                        System.out.println("操作失败");
                    }
                    rs1.previous();
                    rs2.previous();
                }else if(!rs1.next()&&rs2.next()){//年级没有,专业有了
                    String sqlMaxgradeid = "select max(gradeid) from clazz";
                    ResultSet rsMaxgradeid = dbService.querySQL(sqlMaxgradeid);
                    rsMaxgradeid.next();
                    int majorid = rs2.getInt(1);
                    int gradeid = rsMaxgradeid.getInt(1)+1;
                    String sql7 = "insert into clazz(majorid,majorname,clazzname,gradeid,gradename) values('"+majorid+"','"+majorname+"','"+clazzname+"','"+gradeid+"','"+gradename+"')";
                    int i = dbService.updateSQL(sql7);
                    if(i!=0){
                        System.out.println("操作成功");
                    }else {
                        System.out.println("操作失败");
                    }
                    rs1.previous();
                    rs2.previous();
                }else{//年级没有，专业没有
                    String sqlifnull = "select max(gradeid),max(majorid) from clazz";
                    ResultSet rsifnull = dbService.querySQL(sqlifnull);
                    if(!rsifnull.next()){
                        int gradeid = rsifnull.getInt(1)+1;
                        int majorid = rsifnull.getInt(2)+1;
                        String sql8 = "insert into clazz(majorid,majorname,clazzname,gradeid,gradename) values('"+majorid+"','"+majorname+"','"+clazzname+"','"+gradeid+"','"+gradename+"')";
                        int i = dbService.updateSQL(sql8);
                        if(i!=0){
                            System.out.println("操作成功");
                        }else {
                            System.out.println("操作失败");
                        }
                    }else{
                        int gradeid = 1;
                        int majorid = 1;
                        String sql9 = "insert into clazz(majorid,majorname,clazzname,gradeid,gradename) values('"+majorid+"','"+majorname+"','"+clazzname+"','"+gradeid+"','"+gradename+"')";
                        int i = dbService.updateSQL(sql9);
                        if(i!=0){
                            System.out.println("操作成功");
                        }else {
                            System.out.println("操作失败");
                        }
                    }
                    rs1.previous();
                    rs2.previous();
                }
            }

        } catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * @description: <br>
     * 〈新生录入〉
     * @Param: [stunum, stuname, stusex, clazzid]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 11:18
     */
    public void addnewStudent(String stunum,String stuname,String stusex,int clazzid){
        try {
            String sql2 = "select id from student where stunum = '"+stunum+"'";
            ResultSet rs = dbService.querySQL(sql2);
            //校验学生是否已经添加过
            if(!rs.next()){
                String sql1 = "select majorid,gradeid,gradename,majorname,clazzname from clazz where id = '"+clazzid+"'";
                ResultSet rs1 = dbService.querySQL(sql1);
                rs1.next();
                int majorid = rs1.getInt(1);
                int gradeid = rs1.getInt(2);
                String gradename = rs1.getString(3);
                String majorname = rs1.getString(4);
                String clazzname = rs1.getString(5);
                String sql3 = "insert into student(stunum,stuname,stusex,gradeid,majorid,clazzid) values('"+stunum+"','"+stuname+"','"+stusex+"','"+gradeid+"','"+majorid+"','"+clazzid+"')";
                int result = dbService.updateSQL(sql3);
                String sql5 = "select id from student where stunum = '"+stunum+"'";
                ResultSet rs2 = dbService.querySQL(sql5);
                rs2.next();
                int stuid = rs2.getInt(1);
                String sql4 = "insert into studentinfo (stuid,title,interpretation) values ('"+stuid+"','新生录入','学生学号为"+stunum+",学生姓名为"+stuname+",学生性别"+stusex+",就读于"+gradename+"级"+majorname+"专业"+clazzname+"')";
                int result1 = dbService.updateSQL(sql4);
                if(result!=0&&result1!=0){
                    System.out.println("操作成功");
                }else {
                    System.out.println("操作失败");
                }
            }else {
                System.out.println("该学生学号已存在");
            }
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈按照学生编号对学生降班，显示学生是否可以降班〉
     * @Param: [stunum]
     * @Return: int
     * @Author: 文永达
     * @Date: 2021/1/21 20:23
     */
    public int relegation(String stunum){
        try {
            String sql1 = "SELECT c.gradeid,c.majorid FROM student s,clazz c WHERE s.clazzid = c.id AND s.stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            String sql2 = "SELECT max(gradeid) FROM clazz";
            ResultSet rs2 = dbService.querySQL(sql2);
            rs2.next();
            int maxgradeid = rs2.getInt(1);
            if(rs1.next()){
                int gradeid = rs1.getInt(1);
                int majorid = rs1.getInt(2);
                if(gradeid!=maxgradeid){
                    int gradeid1 = gradeid+1;
                    String sql3 = "SELECT c.id,c.gradename,c.majorname,c.clazzname FROM clazz c WHERE c.majorid = "+majorid+" AND c.gradeid = "+gradeid1+"";
                    ResultSet rs3 = dbService.querySQL(sql3);
                    System.out.println("可以降的班如下");
                    System.out.printf("%-4s\t\t%-6s\t\t%-10s\t\t%s\n","班级编号","年级","专业名称","班级");
                    while (rs3.next()){
                        int clazzid = rs3.getInt(1);
                        String gradename = rs3.getString(2);
                        String majorname = rs3.getString(3);
                        String clazzname = rs3.getString(4);
                        System.out.printf("%-4d\t\t%-6S\t\t%-10s\t\t%s\n",clazzid,gradename,majorname,clazzname);
                    }
                    return 1;
                }else{
                    System.out.println("年级已是最低年级无法降班");
                    return 0;
                }
            }else{
                System.out.println("没有找到此学生");
                return 0;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * @description: <br>
     * 〈按照学生编号对学生降班，选择班级编号进行降班 （修改外键）〉
     * @Param: [clazzid, stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 20:24
     */
    public void relegation(int clazzid,String stunum){
        try {
            String sql1 = "SELECT c.id,c.gradeid,c.majorid,c.gradename,c.majorname,c.clazzname FROM clazz c WHERE c.id = "+clazzid+"";
            ResultSet rs1 = dbService.querySQL(sql1);
            rs1.next();
            int id = rs1.getInt(1);
            int gradeid = rs1.getInt(2);
            int majorid = rs1.getInt(3);
            String gradename2 = rs1.getString(4);
            String majorname2 = rs1.getString(5);
            String clazzname2 = rs1.getString(6);
            String sql4 = "SELECT s.id,c.gradename,c.majorname,c.clazzname FROM student s,clazz c WHERE s.clazzid = c.id AND s.stunum = '"+stunum+"'";
            ResultSet rs4 = dbService.querySQL(sql4);
            rs4.next();
            int stuid = rs4.getInt(1);
            String gradename1 = rs4.getString(2);
            String majorname1 = rs4.getString(3);
            String clazzname1 = rs4.getString(4);
            String sql2 = "UPDATE student SET clazzid = "+id+", gradeid = "+gradeid+", majorid = "+majorid+" WHERE stunum = '"+stunum+"'";
            int result2 = dbService.updateSQL(sql2);
            String sql3 = "INSERT INTO studentinfo(stuid,title,interpretation) values ("+stuid+",'学生降班','从"+gradename1+"级"+majorname1+"专业"+clazzname1+"降到"+gradename2+"级"+majorname2+"专业"+clazzname2+"降班')";
            int result3 = dbService.updateSQL(sql3);
            if(result2!=0&&result3!=0){
                System.out.println("操作成功");
            }else {
                System.out.println("操作失败");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈按照学生编号对学生休学〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 21:25
     */
    public void quitschool(String stunum){
        try {
            String sql1 = "SELECT c.gradename,c.majorname,c.clazzname,s.id FROM student s,clazz c WHERE s.clazzid = c.id AND s.stunum='"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                String gradename = rs1.getString(1);
                String majorname = rs1.getString(2);
                String clazzname = rs1.getString(3);
                int stuid = rs1.getInt(4);
                String sql3 = "UPDATE student SET gradeid = NULL,majorid = NULL,clazzid = NULL WHERE stunum='"+stunum+"'";
                int result3 = dbService.updateSQL(sql3);
                if(result3!=0){
                    String sql2 = "INSERT INTO studentinfo(stuid,title,interpretation) values ("+stuid+",'学生休学','从"+gradename+"级"+majorname+"专业"+clazzname+"休学')";
                    int result2 = dbService.updateSQL(sql2);
                    if(result2!=0)
                        System.out.println("操作成功");
                }else {
                    System.out.println("操作失败");
                }
            }else {
                System.out.println("该学生已休学");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈按照学生编号修改学生信息〉
     * @Param: [stunum, stuname, stusex, stunum_huoqu]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/22 12:18
     */
    public void updatestudent(String stunum,String stuname,String stusex,String stunum_huoqu){
        try {
            String sql1 = "SELECT id,stunum,stuname,stusex FROM student WHERE stunum = '" + stunum_huoqu + "'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if (rs1.next()) {
                int stuid1 = rs1.getInt(1);
                String stunum1 = rs1.getString(2);
                String stuname1 = rs1.getString(3);
                String stusex1 = rs1.getString(4);
                //修改学生学号
                if (!stunum.equals("n")) {
                    String sql2 = "UPDATE student SET stunum = '" + stunum + "' WHERE stunum ='" + stunum_huoqu + "'";
                    int result2 = dbService.updateSQL(sql2);
                    String sqlupdateinfo2 = "INSERT INTO studentinfo(stuid,title,interpretation) values (" + stuid1 + ",'学生信息修改','学生学号从" + stunum1 + "修改为" + stunum + "')";
                    int resultupdateinfo2 = dbService.updateSQL(sqlupdateinfo2);
                    if (result2 != 0 && resultupdateinfo2 != 0) {
                        System.out.println("学生学号修改成功");
                    }
                }
                //修改学生姓名
                if (!stuname.equals("n")) {
                    String sql3_1 = "UPDATE student SET stuname = '" + stuname + "' WHERE stunum = '" + stunum_huoqu + "'";
                    int result3_1 = dbService.updateSQL(sql3_1);
                    String sqlupdateinfo3 = "INSERT INTO studentinfo(stuid,title,interpretation) values (" + stuid1 + ",'学生信息修改','学生姓名从" + stuname1 + "修改为" + stuname + "')";
                    int resultupdateinfo3 = dbService.updateSQL(sqlupdateinfo3);
                    if (result3_1 != 0 && resultupdateinfo3 != 0) {
                        System.out.println("学生姓名修改成功");
                    }
                }

                //修改学生性别
                if (!stusex.equals("n")) {
                    String sql4_1 = "UPDATE student SET stusex = '" + stusex + "' WHERE stunum = '" + stunum_huoqu + "'";
                    int result4_1 = dbService.updateSQL(sql4_1);
                    String sqlupdateinfo4 = "INSERT INTO studentinfo(stuid,title,interpretation) values ("+stuid1+",'学生信息修改','学生性别从" + stusex1 + "修改为" + stusex + "')";
                    int resultupdateinfo4 = dbService.updateSQL(sqlupdateinfo4);
                    if (result4_1 != 0 && resultupdateinfo4 != 0) {
                        System.out.println("学生性别修改成功");
                    }
                }
            }else {
                System.out.println("操作失败");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    /**
     * @description: <br>
     * 〈按照学生编号对学生复课〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/22 12:53
     */
    public void resumeschool(String stunum){
        try {
            String sql1 = "SELECT si.interpretation,s.id FROM studentinfo si,student s where s.id = si.stuid AND s.stunum = '"+stunum+"' AND si.title = '学生休学'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                int stuid = rs1.getInt(2);
                String s1 = rs1.getString(1);
                String []str1 = s1.split("从");
                String s2 = str1[1];
                String []str2 = s2.split("级");
                String s3 = str2[1];
                String []str3 = s3.split("专业");
                String s4 =str3[1];
                String []str4 = s4.split("休学");
                String sql2 = "SELECT id,gradeid,majorid FROM clazz WHERE gradename = '"+str2[0]+"' AND majorname = '"+str3[0]+"' AND clazzname = '"+str4[0]+"'";
                ResultSet rs2 = dbService.querySQL(sql2);
                if(rs2.next()){
                    int clazzid = rs2.getInt(1);
                    int gradeid = rs2.getInt(2);
                    int majorid = rs2.getInt(3);
                    String sql3 = "UPDATE student SET gradeid = "+gradeid+", majorid = "+majorid+", clazzid = "+clazzid+" WHERE stunum = '"+stunum+"'";
                    int result3 = dbService.updateSQL(sql3);
                    String sql4 = "INSERT INTO studentinfo(stuid,title,interpretation) values ("+stuid+",'学生复课','已回到"+str2[0]+"级"+str3[0]+"专业"+str4[0]+"复课')";
                    int result4 = dbService.updateSQL(sql4);
                    if(result3!=0&&result4!=0){
                        System.out.println("操作成功");
                    }else{
                        System.out.println("操作失败");
                    }
                }else{
                    System.out.println("之前休学的班级已撤班");
                }

            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈根据编号调整某个学生的专业〉
     * @Param: [stunum, clazzid1, num]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 9:24
     */
    public void updateMajor(String stunum,int clazzid1,int num){
        try {
            if(clazzid1==0&&num==0){
                String sql1 = "SELECT gradeid,majorid,clazzid FROM student WHERE stunum = '"+stunum+"'";
                ResultSet rs1 = dbService.querySQL(sql1);
                rs1.next();
                int gradeid = rs1.getInt(1);
                int majorid = rs1.getInt(2);
                int clazzid = rs1.getInt(3);
                String sql2 = "SELECT c.id,c.majorid,c.clazzname,c.majorname FROM clazz c WHERE c.gradeid = "+gradeid+"";
                ResultSet rs2 = dbService.querySQL(sql2);
                System.out.println("可以转的专业如下：");
                System.out.println("班级编号"+"\t"+"专业编号"+"\t"+"专业名称"+"\t"+"班级");
                while (rs2.next()){
                    int clazzid2 = rs2.getInt(1);
                    int majorid2 = rs2.getInt(2);
                    String clazzname = rs2.getString(3);
                    String majorname = rs2.getString(4);
                    if(majorid!=majorid2&&clazzid!=clazzid2){
                        System.out.println(clazzid2+"\t"+majorid2+"\t"+majorname+"\t"+clazzname);
                    }
                }
            }else if(num==1){
                String sql3 = "SELECT majorid,gradename,majorname,clazzname FROM clazz WHERE id = "+clazzid1+"";
                ResultSet rs3 = dbService.querySQL(sql3);
                if(rs3.next()){
                    int majorid = rs3.getInt(1);
                    String gradename = rs3.getString(2);
                    String majorname = rs3.getString(3);
                    String clazzname = rs3.getString(4);
                    String sql4 = "UPDATE student SET majorid = "+majorid+",clazzid = "+clazzid1+" WHERE stunum = '"+stunum+"'";
                    int result4 = dbService.updateSQL(sql4);
                    String sql5 = "INSERT INTO studentinfo(stuid,title,interpretation) values ((select id from student where stunum = '"+stunum+"'),'专业调剂','学生学号为"+stunum+",调剂到"+gradename+"级"+majorname+"专业"+clazzname+"')";
                    int result5 = dbService.updateSQL(sql5);
                    if(result4!=0&&result5!=0){
                        System.out.println("操作成功");
                    }else {
                        System.out.println("操作失败");
                    }
                }else{
                    System.out.println("没有找到班级请确认编号是否输入错误");
                }
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈根据学科编号选择进行选修课选课 〉
     * @Param: [stunum, courseid]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 11:31
     */
    public void updateOptionalCourseBystunum(String stunum,int courseid){
        try {
            String sql1 = "SELECT id FROM student WHERE stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            rs1.next();
            int stuid = rs1.getInt(1);
            String sql2 = "INSERT INTO score(stuid, courseid) VALUES ("+stuid+","+courseid+")";
            int result2 = dbService.updateSQL(sql2);
            if(result2!=0){
                System.out.println("选课成功");
            }else{
                System.out.println("选课失败");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }


}
